import datetime
from io import BytesIO

from flask import request, Response
from openpyxl import Workbook
from openpyxl.styles import Font, PatternFill, Border, Side, Alignment

from tzq.document_management_system import dms_bp
from tzq.document_management_system.model.function_model import Function, FunctionPermission, FunctionCatalog, \
    FunctionDimension


@dms_bp.route('/api/exportFunctionAllDataToExcel', methods=['GET', 'POST'])
def export_function_all_data_to_excel():
    # 导出 function 的所有数据
    # 入参
    principal = request.values.get("principal")
    online_date = request.values.get("online_date")
    created_by = request.values.get("created_by")

    # 定义Excel表头样式
    header_font = Font(name='Arial', bold=True, color="000000", size=12)
    header_fill = PatternFill(start_color="FF0000", end_color="FF0000", fill_type="solid")
    thin_border = Border(
        left=Side(style='thin'),
        right=Side(style='thin'),
        top=Side(style='thin'),
        bottom=Side(style='thin')
    )
    center_alignment = Alignment(horizontal="center", vertical="center")

    # 1、导出 function 的数据
    query = Function.query
    query = query.filter(Function.delete_flag == 'N')
    if principal: query = query.filter(Function.principal.ilike(f'%{principal}%'))
    if online_date: query = query.filter(Function.online_date == f'{online_date}')
    if created_by: query = query.filter(Function.created_by.ilike(f'%{created_by}%'))
    query = query.order_by(Function.online_date.desc(), Function.last_update_date.desc())
    query = query.paginate(page=1, per_page=1000000, error_out=False)
    wb = Workbook()
    ws = wb.active
    ws.title = "Functions Basic Information"
    # 写入表头
    headers = [
        'Function Name',
        'Function Name En',
        'Description',
        'DescriptionEn',
        'EnableFlag',
        '责任人', '上线日期'
    ]
    ws.append(headers)
    # 为表头行（第1行）设置样式
    for col_num, header in enumerate(headers, 1):
        cell = ws.cell(row=1, column=col_num)
        cell.font = header_font
        cell.fill = header_fill
        cell.border = thin_border
        cell.alignment = center_alignment
    # 写入数据
    for item in query:
        ws.append([
            item.function_name,
            '',
            '',
            '',
            'Y',
            item.principal,
            item.online_date.strftime('%Y-%m-%d')
        ])
    # 冻结首行（将 A2 设为冻结点，其上方的行会被冻结）
    ws.freeze_panes = 'A2'
    # 启用自动筛选：从 A1 到最后一列最后一行
    last_col_letter = ws.cell(row=1, column=ws.max_column).column_letter
    last_row = ws.max_row
    ws.auto_filter.ref = f"A1:{last_col_letter}{last_row}"
    # 调整列宽
    ws.column_dimensions['A'].width = 45  # Function Name
    ws.column_dimensions['B'].width = 23  # Function Name En
    ws.column_dimensions['C'].width = 16  # Description
    ws.column_dimensions['D'].width = 18  # DescriptionEn
    ws.column_dimensions['E'].width = 16  # EnableFlag
    ws.column_dimensions['F'].width = 13  # 责任人
    ws.column_dimensions['G'].width = 13  # 上线日期
    # 设置某几列单元格居中显示
    for col in ['E', 'G']:
        for row in ws.iter_rows(min_col=ws[col + '1'].column, max_col=ws[col + '1'].column):
            for cell in row:
                cell.alignment = Alignment(horizontal='center', vertical='center')

    # 2、导出 权限集权限点 的数据
    query = FunctionPermission.query
    query = query.filter(FunctionPermission.delete_flag == 'N')
    if principal: query = query.filter(FunctionPermission.principal.ilike(f'%{principal}%'))
    if online_date: query = query.filter(FunctionPermission.online_date == f'{online_date}')
    if created_by: query = query.filter(FunctionPermission.created_by.ilike(f'%{created_by}%'))
    query = query.order_by(FunctionPermission.online_date.desc(), FunctionPermission.last_update_date.desc())
    query = query.paginate(page=1, per_page=1000000, error_out=False)
    # 创建第二个工作表
    ws2 = wb.create_sheet("Functions Permission")
    # 写入表头
    headers = [
        'Function Name',
        'Resource Code',
        'Permession Operation Code',
        'Resource Type',
        'Bastion Sharing Name',
        '责任人', '上线日期'
    ]
    ws2.append(headers)
    # 为表头行（第1行）设置样式
    for col_num, header in enumerate(headers, 1):
        cell = ws2.cell(row=1, column=col_num)
        cell.font = header_font
        cell.fill = header_fill
        cell.border = thin_border
        cell.alignment = center_alignment
    # 写入数据
    for item in query:
        ws2.append([
            item.function_name,
            item.r_uid,
            item.pr_uid,
            'Service',
            item.app_name,
            item.principal,
            item.online_date.strftime('%Y-%m-%d')
        ])
    # 冻结首行（将 A2 设为冻结点，其上方的行会被冻结）
    ws2.freeze_panes = 'A2'
    # 启用自动筛选：从 A1 到最后一列最后一行
    last_col_letter = ws2.cell(row=1, column=ws2.max_column).column_letter
    last_row = ws2.max_row
    ws2.auto_filter.ref = f"A1:{last_col_letter}{last_row}"
    # 调整列宽
    ws2.column_dimensions['A'].width = 45  # Function Name
    ws2.column_dimensions['B'].width = 45  # Resource Code
    ws2.column_dimensions['C'].width = 50  # Permession Operation Code
    ws2.column_dimensions['D'].width = 20  # Resource Type
    ws2.column_dimensions['E'].width = 27  # Bastion Sharing Name
    ws2.column_dimensions['F'].width = 13  # 责任人
    ws2.column_dimensions['G'].width = 13  # 上线日期
    # 设置某几列单元格居中显示
    for col in ['D', 'G']:
        for row in ws2.iter_rows(min_col=ws2[col + '1'].column, max_col=ws2[col + '1'].column):
            for cell in row:
                cell.alignment = Alignment(horizontal='center', vertical='center')

    # 3、权限集菜单
    query = FunctionCatalog.query
    query = query.filter(FunctionCatalog.delete_flag == 'N')
    if principal: query = query.filter(FunctionCatalog.principal.ilike(f'%{principal}%'))
    if online_date: query = query.filter(FunctionCatalog.online_date == f'{online_date}')
    if created_by: query = query.filter(FunctionCatalog.created_by.ilike(f'%{created_by}%'))
    query = query.order_by(FunctionCatalog.online_date.desc(), FunctionCatalog.last_update_date.desc())
    query = query.paginate(page=1, per_page=1000000, error_out=False)
    # 创建第三个工作表
    ws3 = wb.create_sheet("Functions Catalog")
    # 写入表头
    headers = [
        'Function Name',
        'Parent Node Name',
        'Name',
        'Ctype',
        '责任人', '上线日期'
    ]
    ws3.append(headers)
    # 为表头行（第1行）设置样式
    for col_num, header in enumerate(headers, 1):
        cell = ws3.cell(row=1, column=col_num)
        cell.font = header_font
        cell.fill = header_fill
        cell.border = thin_border
        cell.alignment = center_alignment
    # 写入数据
    for item in query:
        ws3.append([
            item.function_name,
            item.parent_node_name,
            item.name,
            'saasoneservice',
            item.principal,
            item.online_date.strftime('%Y-%m-%d')
        ])
    # 冻结首行（将 A2 设为冻结点，其上方的行会被冻结）
    ws3.freeze_panes = 'A2'
    # 启用自动筛选：从 A1 到最后一列最后一行
    last_col_letter = ws3.cell(row=1, column=ws3.max_column).column_letter
    last_row = ws3.max_row
    ws3.auto_filter.ref = f"A1:{last_col_letter}{last_row}"
    # 调整列宽
    ws3.column_dimensions['A'].width = 45  # Function Name
    ws3.column_dimensions['B'].width = 65  # Parent Node Name
    ws3.column_dimensions['C'].width = 65  # Name
    ws3.column_dimensions['D'].width = 20  # Ctype
    ws3.column_dimensions['E'].width = 15  # 责任人
    ws3.column_dimensions['F'].width = 15  # 上线日期
    # 设置某几列单元格居中显示
    for col in ['D', 'F']:
        for row in ws3.iter_rows(min_col=ws3[col + '1'].column, max_col=ws3[col + '1'].column):
            for cell in row:
                cell.alignment = Alignment(horizontal='center', vertical='center')

    # 4、权限集维度
    query = FunctionDimension.query
    query = query.filter(FunctionDimension.delete_flag == 'N')
    if principal: query = query.filter(FunctionDimension.principal.ilike(f'%{principal}%'))
    if online_date: query = query.filter(FunctionDimension.online_date == f'{online_date}')
    if created_by: query = query.filter(FunctionDimension.created_by.ilike(f'%{created_by}%'))
    query = query.order_by(FunctionDimension.online_date.desc(), FunctionDimension.last_update_date.desc())
    query = query.paginate(page=1, per_page=1000000, error_out=False)
    # 创建第四个工作表
    ws4 = wb.create_sheet("Functions Dimension")
    # 写入表头
    headers = [
        'Function Name',
        'Dimension Code',
        '责任人', '上线日期'
    ]
    ws4.append(headers)
    # 为表头行（第1行）设置样式
    for col_num, header in enumerate(headers, 1):
        cell = ws4.cell(row=1, column=col_num)
        cell.font = header_font
        cell.fill = header_fill
        cell.border = thin_border
        cell.alignment = center_alignment
    # 写入数据
    for item in query:
        ws4.append([
            item.function_name,
            item.dimension_code,
            item.principal,
            item.online_date.strftime('%Y-%m-%d')
        ])
    # 冻结首行（将 A2 设为冻结点，其上方的行会被冻结）
    ws4.freeze_panes = 'A2'
    # 启用自动筛选：从 A1 到最后一列最后一行
    last_col_letter = ws4.cell(row=1, column=ws4.max_column).column_letter
    last_row = ws4.max_row
    ws4.auto_filter.ref = f"A1:{last_col_letter}{last_row}"
    # 调整列宽
    ws4.column_dimensions['A'].width = 50  # Function Name
    ws4.column_dimensions['B'].width = 35  # Dimension Code
    ws4.column_dimensions['C'].width = 15  # 责任人
    ws4.column_dimensions['D'].width = 15  # 上线日期
    # 设置某几列单元格居中显示
    for col in ['D']:
        for row in ws4.iter_rows(min_col=ws4[col + '1'].column, max_col=ws4[col + '1'].column):
            for cell in row:
                cell.alignment = Alignment(horizontal='center', vertical='center')

    # 5、Functions FrontendResource
    # 创建第五个工作表
    ws5 = wb.create_sheet("Functions FrontendResource")
    # 写入表头
    headers = [
        'Function Name',
        'Code',
        'AppName'
    ]
    ws5.append(headers)

    date_string = datetime.datetime.now().strftime("%Y%m%d%H%M%S")

    # 将工作簿写入 BytesIO 流
    output = BytesIO()
    wb.save(output)
    output.seek(0)

    if online_date:
        version_string = "(v." + online_date + ")-"
    else:
        version_string = "ALL-"

    # 使用 Response 返回 Excel 文件
    return Response(
        output.getvalue(),
        mimetype='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet',
        headers={
            "Content-Disposition": "attachment; filename=jalor-Function-" + version_string + date_string + ".xlsx"
            # 文件名不能为中文，不然报错了
        }
    )
